package de.bwv.javaproject.FunnyTokens.model.persistence;

import java.awt.Dimension;
import java.awt.Point;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;

import de.bwv.javaproject.FunnyTokens.model.Flag;
import de.bwv.javaproject.FunnyTokens.model.Game;
import de.bwv.javaproject.FunnyTokens.model.PlayingCard;
import de.bwv.javaproject.FunnyTokens.model.Turn;
import de.bwv.javaproject.FunnyTokens.model.factory.board.Board;
import de.bwv.javaproject.FunnyTokens.model.factory.board.Field;
import de.bwv.javaproject.FunnyTokens.model.factory.rulecontroller.SolveResult.Difficulty;
import de.bwv.javaproject.FunnyTokens.model.factory.token.Token;

public class JDBCPersistence implements IDataPersistence {

	private enum TurnType{
		Game,
		Flag
	}
	
	private Boolean logging = false;
    
    private Connection connection;
    
    private PreparedStatement selectLastId;
    
    private PreparedStatement deleteField;
    private PreparedStatement insertField;
    private PreparedStatement selectField;
    
    private PreparedStatement deleteFlag;
    private PreparedStatement insertFlag;
    private PreparedStatement selectFlag;
    
    private PreparedStatement insertGame;
    private PreparedStatement deleteGame;
    private PreparedStatement selectGameById;
    private PreparedStatement selectGames;
    private PreparedStatement selectGameByName;

    private PreparedStatement deleteTurnByFsGame;
    private PreparedStatement deleteTurnByFsFlag;
    private PreparedStatement selectTurnByFsGame;
    private PreparedStatement selectTurnByFsFlag;
    private PreparedStatement insertTurnWithFsFlag;
    private PreparedStatement insertTurnWithFsGame;
    
    private PreparedStatement insertPlayingCard;
    private PreparedStatement deletePlayingCard;
    private PreparedStatement selectPlayingCardById;
    private PreparedStatement selectPlayingCards;
    private PreparedStatement selectPlayingCardByName;
    
    private PreparedStatement insertBoard;
    private PreparedStatement selectBoardById;
    private PreparedStatement deleteBoard;
    
    public JDBCPersistence(){
    	try
        {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        }
        catch(ClassNotFoundException e)
        {
            System.err.println("JdbcOdbc Bridge Driver not found!");
        }
        try
        {
            connection = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=dbase/funnytokens.mdb;DriverID=22;READONLY=false;}","","");
            
            selectLastId = connection.prepareStatement("SELECT @@identity");
            
            deleteField = connection.prepareStatement("DELETE FROM Field WHERE FS_Board=?");
            insertField = connection.prepareStatement("INSERT INTO Field (FS_Board, Token, PointX, PointY) VALUES(?,?,?,?)");
            selectField = connection.prepareStatement("SELECT * FROM Field WHERE FS_Board=?");
            
            deleteFlag = connection.prepareStatement("DELETE FROM Flag WHERE ID_Flag=?");
            insertFlag = connection.prepareStatement("INSERT INTO Flag (FS_Game, NoError) VALUES (?,?)");
            selectFlag = connection.prepareStatement("SELECT * FROM Flag WHERE FS_Game=? AND NoError=?");
            
            insertGame = connection.prepareStatement("INSERT INTO Game (FS_PlayingCard, Gamename, FactoryName, Gamedate, Solved) VALUES (?,?,?,?,?)");
            deleteGame = connection.prepareStatement("DELETE FROM Game WHERE ID_Game=?");
            selectGameById = connection.prepareStatement("SELECT * FROM Game WHERE ID_Game=?");
            selectGames = connection.prepareStatement("SELECT * FROM Game");
            selectGameByName = connection.prepareStatement("SELECT * FROM Game WHERE Gamename=?");
            
            deleteTurnByFsGame = connection.prepareStatement("DELETE FROM Turn WHERE FS_Game=?");
            deleteTurnByFsFlag = connection.prepareStatement("DELETE FROM Turn WHERE FS_Flag=?");
            selectTurnByFsGame = connection.prepareStatement("SELECT * FROM Turn WHERE FS_Game=?");
            selectTurnByFsFlag = connection.prepareStatement("SELECT * FROM Turn WHERE FS_Flag=?");
            insertTurnWithFsGame = connection.prepareStatement("INSERT INTO Turn (FS_Game , TurnX, TurnY, Error, OldToken, NewToken) VALUES (?,?,?,?,?,?)");
            insertTurnWithFsFlag = connection.prepareStatement("INSERT INTO Turn (FS_Flag , TurnX, TurnY, Error, OldToken, NewToken) VALUES (?,?,?,?,?,?)");
            
            insertPlayingCard = connection.prepareStatement("INSERT INTO PlayingCard (PlayingCardName, Difficulty, PlayingCardDate, Ready, FS_Board) VALUES(?,?,?,?,?)");
            deletePlayingCard = connection.prepareStatement("DELETE FROM PlayingCard WHERE ID_PlayingCard=?");
            selectPlayingCardById = connection.prepareStatement("SELECT * FROM PlayingCard WHERE ID_PlayingCard=?");
            selectPlayingCards = connection.prepareStatement("SELECT * FROM PlayingCard");
            selectPlayingCardByName = connection.prepareStatement("SELECT * FROM PlayingCard WHERE PlayingCardName=?");
            
            insertBoard = connection.prepareStatement("INSERT INTO Board (UniqueClassName, Width, Height) VALUES(?,?,?)");
            selectBoardById = connection.prepareStatement("SELECT * FROM Board WHERE ID_Board=?");
            deleteBoard = connection.prepareStatement("DELETE FROM Board WHERE ID_Board=?");          
            
        } catch(SQLException e)
        {
            e.printStackTrace();
        }
			
    }
    
    private void cleanStatement(PreparedStatement statement){
    	try {
			statement.clearBatch();
	    	statement.clearParameters();
	    	statement.clearWarnings();
		} catch (SQLException e) {
			e.printStackTrace();
		}
    }
    
    private void deleteField(long BoardId) throws SQLException{
    	cleanStatement(deleteField);
    	
    	deleteField.setInt(1, LongToInt(BoardId));
    	
    	int result = deleteField.executeUpdate();
        log("Delete Field Result -> " + result);
    }
    
    private void insertField(int BoardId, Field field) throws SQLException{
    	cleanStatement(insertField);
    	
    	insertField.setInt(1, BoardId);
    	insertField.setString(2, field.getToken().getClass().getCanonicalName());
    	insertField.setDouble(3, field.getPoint().x);
    	insertField.setInt(4, field.getPoint().y);

    	int result = insertField.executeUpdate();

        log("Insert Field Result -> " + result);
    }
    
	private List<Field> getFieldsByBoardId(long boardId) throws SQLException{
    	cleanStatement(selectField);
    	
    	selectField.setInt(1,LongToInt(boardId));
    	
    	ResultSet rs = selectField.executeQuery();
    	
    	List<Field> fields = new ArrayList<Field>();
    	while(rs.next()){
    		fields.add(FieldMapping(rs));
    	}
    	return fields;
    }
    
    private void deleteFlag(long flagId) throws SQLException{
    	cleanStatement(deleteFlag);
    	
    	deleteFlag.setInt(1,LongToInt(flagId));
    	
    	int result = deleteFlag.executeUpdate();
    	log("Delete Flag FlagId="+ flagId + " Result->" + result);  
    	if(result == 1){
    		deleteTurns(TurnType.Flag, flagId);
    	}
    }
    
    private long insertFlag(Flag flag, TurnType turnType, long GameId) throws SQLException{
    	return insertFlag(flag, turnType, GameId, false);    	
    }
    
    private long insertFlag(Flag flag, TurnType turnType,final long GameId, boolean NoError) throws SQLException{
    	cleanStatement(insertFlag);
    	
    	insertFlag.setInt(1, LongToInt(GameId));
    	insertFlag.setBoolean(2, NoError);
    	
    	int result = insertFlag.executeUpdate();
    	
    	log("Insert Flag Result -> " + result);
    	long flagId = getLetzteId();
    	log("FlagId = " + flagId);
    	insertTurn(flag.getTurns(), turnType, flagId);
    	return flagId;
    }

	private Flag getNoErrorFlag(long gameId) throws SQLException{
		List<Flag> flags = getFlagsByGameId(gameId, true);
		if(flags.size() == 1){
			return flags.get(0);
		}
		return null;
	}
	
	private List<Flag> getFlagsByGameId(long gameId, boolean noError)throws SQLException{
		cleanStatement(selectFlag);
		selectFlag.setInt(1, LongToInt(gameId));
		selectFlag.setBoolean(2, noError);
		
		ResultSet rs = selectFlag.executeQuery();
		
		List<Flag> flags = new ArrayList<Flag>();
		while(rs.next()){
			flags.add(FlagMapping(rs));
		}
		return flags;		
	}
	
    private void insertGame(Game game) throws SQLException{
    	cleanStatement(insertGame);
    	
    	insertGame.setInt(1,LongToInt( game.getGameCard().getId()));
    	insertGame.setString(2, game.getName());
    	insertGame.setString(3, game.getGameFactory().getClass().getCanonicalName().split("\\.")[game.getGameFactory().getClass().getCanonicalName().split("\\.").length-1]);
    	insertGame.setTimestamp(4, new Timestamp(game.getDate().getTimeInMillis()));
    	insertGame.setBoolean(5, game.isSolved());

    	int result = insertGame.executeUpdate();
    	
        log("Insert Game : Result -> " + result);
        long GameId = getLetzteId();
        log("GameId = " +  GameId);
        
        if(game.getNoErrorFlag() != null){
        	insertFlag(game.getNoErrorFlag(),TurnType.Flag, GameId, true);
        }
        
        insertTurn(game.getDoneTurns(), TurnType.Game, GameId);
        
        for(Flag flag : game.getDoneFlags()){
        	insertFlag(flag, TurnType.Flag, GameId);
        }
    }
    
    private void deleteGame(Game game) throws SQLException {
    	for(Flag flag : game.getDoneFlags()){
    		deleteFlag(flag.getId());
    	}

    	deleteTurns(TurnType.Game, game.getId());
    	
    	if(game.getNoErrorFlag() != null){
    		deleteFlag(game.getNoErrorFlag().getId());
    	}
    	
    	cleanStatement(deleteGame);
    	
    	deleteGame.setInt(1, LongToInt( game.getId()));
    	
    	int result = deleteGame.executeUpdate();
    	
    	log("Delete Game GameId=" + game.getId() + " Result: " + result);  
    	// sleep();
    }
    
    private void deleteTurns(TurnType tt, long TypeId) throws SQLException{
    	PreparedStatement statement = null;
    	switch(tt){
    	case Flag:
    		statement = deleteTurnByFsFlag;
    		break;
    	case Game:
    		statement = deleteTurnByFsGame;
    	}
    	cleanStatement(statement);
    	
    	statement.setInt(1,LongToInt( TypeId));
    	
    	int result = statement.executeUpdate();
    	log("Delete Turn mit tt=" + tt.toString() + " und TypeId=" + TypeId + " -> Result:" + result);
    }

	private List<Turn> getTurns(TurnType type, long id) throws SQLException{
		PreparedStatement statement = null;
		switch(type){
		case Flag:
			statement = selectTurnByFsFlag;
			break;
		case Game:
			statement = selectTurnByFsGame;
			break;
		}
		
		cleanStatement(statement);
		
		statement.setInt(1,LongToInt(id));
		ResultSet rs = statement.executeQuery();
		
		List<Turn> turns = new ArrayList<Turn>();
		while(rs.next()){
			turns.add(TurnMapping(rs));
		}
				
		return turns;
	}
	
    
    private void insertTurn(final List<Turn> turns, TurnType gameOrTurn, Long id) throws SQLException{
    	PreparedStatement statement = null;
    	switch(gameOrTurn){
    	case Flag:
    		statement = insertTurnWithFsFlag;
    		break;
    	case Game:
    		statement = insertTurnWithFsGame;
    		break;
    	}
    	
    	cleanStatement(statement);
    	
    	long time =  Calendar.getInstance().getTimeInMillis();
    	
    	for(Turn turn : turns){
    		statement.clearParameters();
    		statement.setInt(1,LongToInt( id));
    		statement.setInt(2, turn.getPoint().x);
    		statement.setInt(3, turn.getPoint().y);
    		statement.setBoolean(4, turn.isError());
    		statement.setString(5, TokenToString(turn.getOldToken()));
    		statement.setString(6, TokenToString(turn.getNewToken()));
    		statement.addBatch();
    	}
    	int[] result = statement.executeBatch();
    	
    	log("insertTurn runtime " + (Calendar.getInstance().getTimeInMillis()-time));
    	log("insertTurn Result ->" + result.toString());
    }
    
    
    private int insertPlayingCard(int boardId, PlayingCard playingCard) throws SQLException{
    	
    	cleanStatement(insertPlayingCard);

    	int diff = 0;
    	if(playingCard.getDifficulty() != null){
    		diff = playingCard.getDifficulty().ordinal();
    	}
    	insertPlayingCard.setString(1, playingCard.getName());
    	insertPlayingCard.setInt(2, diff);
    	insertPlayingCard.setTimestamp(3,new Timestamp( playingCard.getDate().getTimeInMillis()));
    	insertPlayingCard.setBoolean(4, playingCard.isReady());
    	insertPlayingCard.setInt(5, boardId);
    	
    	int result = insertPlayingCard.executeUpdate();    	
        System.err.println("Insert PlayingCard : Result -> " + result);
        return getLetzteId();
    }
    
    private void deletePlayingCard(PlayingCard pc)throws SQLException{
    	deleteBoard(pc.getBoard());
    	cleanStatement(deletePlayingCard);
    	
    	deletePlayingCard.setInt(1,LongToInt( pc.getId()));
    	
    	int result = deletePlayingCard.executeUpdate();    	
    	
    	log("Delete PlayingCard PlayingCardId="+ pc.getId() + " Result:"+ result);    	
    }
    
    private int insertBoard(final Board board) throws SQLException{
    	log("Insert Board");
    	cleanStatement(insertBoard);
    	
    	insertBoard.setString(1, board.getClass().getCanonicalName());
    	insertBoard.setInt(2, board.getDimension().width);
    	insertBoard.setInt(3, board.getDimension().height);

    	int boardId = 0;
    	int result = insertBoard.executeUpdate();
    	
    	log("Insert Board : Result ->" + result);
    	if(result == 1){
    		boardId = getLetzteId();
    		log("Letzte ID: " + boardId);
    		for(Field field : board.getGameMatrix().values()){
    			insertField(boardId, field);
    		}
    	}
    	return boardId;
    }
    
    
    private Board getBoardByBoardId(Long Id)throws SQLException{
    	cleanStatement(selectBoardById);
    	
    	selectBoardById.setInt(1,LongToInt( Id));
    	
    	ResultSet rs = selectBoardById.executeQuery();
    	
    	List<Board> boards = new ArrayList<Board>();
    	
    	while(rs.next()){
    		boards.add(BoardMapping(rs));
    	}
    	if(boards.size() > 0){
    		return boards.get(0);
    	}
    	return null;
    }
    
    private void deleteBoard(Board board) throws SQLException{
    	deleteField(board.getId());
    	
    	cleanStatement(deleteBoard);
    	
    	deleteBoard.setInt(1,LongToInt( board.getId()));
    	
    	int result = deleteBoard.executeUpdate();
    	
    	log("Delete Board BoardId=" + board.getId() + " Result:" + result);
    }
        
	@Override
	public void editGame(Game g) {
		
	}

	@Override
	public void editPlayingCard(PlayingCard pc) {

	}

	@Override
	public Game getGameById(long id) throws SQLException {
		cleanStatement(selectGameById);
		selectGameById.setInt(1,LongToInt(id));
		ResultSet rs = selectGameById.executeQuery();
		while(rs.next()){
			return GameMapping(rs);
		}
		return null;
	}

	@Override
	public PlayingCard getPlayingCardById(long id) throws SQLException {
		log("getPlayingCardById Id=" + id);
		cleanStatement(selectPlayingCardById);
		selectPlayingCardById.setInt(1, LongToInt(id));
		ResultSet rs = selectPlayingCardById.executeQuery();
		while(rs.next()){
			return PlayingCardMapping(rs);
		}
		return null;
	}

	@Override
	public List<Game> loadGames() throws SQLException {
		cleanStatement(selectGames);
		ResultSet rs = selectGames.executeQuery();
		List<Game> games = new ArrayList<Game>();
		while(rs.next()){
			games.add(GameMapping(rs));
		}		
		return games;
	}
	
	@Override
	public List<PlayingCard> loadPlayingCards() throws SQLException {
		cleanStatement(selectPlayingCards);
		ResultSet rs = selectPlayingCards.executeQuery();
		List<PlayingCard> playingCards = new ArrayList<PlayingCard>();
		while(rs.next()){
			playingCards.add(PlayingCardMapping(rs));
		}
		return playingCards;
	}

	@Override
	public void removeGame(Game g) throws SQLException {
		deleteGame(g);
	}

	@Override
	public void removePlayingCard(PlayingCard pc) throws SQLException {
		deletePlayingCard(pc);		
	}

	@Override
	public void saveGame(Game g) throws SQLException {	
		log("saveGame");
	    insertGame(g);
	}

	@Override
	public void savePlayingCard(PlayingCard pc) throws SQLException {
		log("savePlayingCard");
		int boardId = insertBoard(pc.getBoard());
		log("boardId = " + boardId);
		if(boardId != -1){
			insertPlayingCard(boardId, pc);
		}
	}

	private Calendar parseDate(long timestamp){
		Calendar cal = new GregorianCalendar();
		cal.setTime(new Date(timestamp));
		return cal;
	}
	
	private int LongToInt(Long l){
		return new Integer(l.toString());
	}
	
	private void log(String msg){
		if(logging){
			System.err.println("LOG: " + msg);
		}
	}
	private String TokenToString(Token token){
		if(token != null){
			return token.getClass().getCanonicalName();
		}	
		return "";
	}
	
	private Token StringToToken(String tokenString){
		Token token = null;
		try {
			token =  (Token) Class.forName(tokenString).newInstance();
		} catch (InstantiationException e) {
			// e.printStackTrace();
		} catch (IllegalAccessException e) {
			// e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// e.printStackTrace();
		}
		return token;
	}

    private int getLetzteId() throws SQLException{
    	cleanStatement(selectLastId);
    	ResultSet rs =  selectLastId.executeQuery();
    	while(rs.next()){
    		return rs.getInt(1);
    	}
    	return 0;
    }
    
	private PlayingCard PlayingCardMapping (ResultSet rs) throws SQLException {
        	PlayingCard playingCard = new PlayingCard(getBoardByBoardId(rs.getLong("FS_Board")));
            playingCard.setId(rs.getLong("ID_PlayingCard"));
            playingCard.setDate(parseDate(rs.getTimestamp("PlayingCardDate").getTime()));
            playingCard.setName(rs.getString("PlayingCardName"));
            playingCard.setReady(rs.getBoolean("Ready"));
            playingCard.setDifficulty(Difficulty.values()[rs.getInt("Difficulty")]);
            return playingCard;     
    }
	
	private Board BoardMapping(ResultSet rs) throws SQLException {
        	Board board = null;
			try {
				board = (Board) Class.forName(rs.getString("UniqueClassName")).newInstance();
			} catch (InstantiationException e) {
				// e.printStackTrace();
			} catch (IllegalAccessException e) {
				// e.printStackTrace();
			} catch (ClassNotFoundException e) {
				// e.printStackTrace();
			}
        	board.setId(rs.getLong("ID_Board"));
        	board.setDimension(new Dimension(LongToInt(rs.getLong("Width")), rs.getInt("Height")));
        	List<Field> fields = getFieldsByBoardId(board.getId());
        	for(Field field: fields){
        		board.getGameMatrix().put(field.getPoint(), field);
        	}
            return board;
    }
	
	private Field FieldMapping(ResultSet rs) throws SQLException {
        Field field = new Field(
            	new Point(rs.getInt("PointX"),rs.getInt("PointY")),
            	StringToToken(rs.getString("Token"))
            );
        field.setId(rs.getLong("ID_Field"));
        return field;            
	}
	
	private Turn TurnMapping(ResultSet rs) throws SQLException {
		 	 Turn turn = new Turn(
					 StringToToken(rs.getString("OldToken")),
					 StringToToken(rs.getString("NewToken")),
					 new Point(rs.getInt("TurnX"),rs.getInt("TurnY"))
					 );
			 turn.setId(rs.getLong("ID_Turn"));
			 turn.setError(rs.getBoolean("Error"));
			 return turn;		 
	}
	
	private Flag FlagMapping(ResultSet rs) throws SQLException {
			 Flag flag = new Flag();
			 flag.setId(rs.getInt("ID_Flag"));		
			 flag.setTurns(getTurns(TurnType.Flag, flag.getId()));	
			 return flag;
	}
	
	private Game GameMapping(ResultSet rs) throws SQLException {
            Game game = new Game(rs.getString("FactoryName"));
            game.setId(rs.getLong("ID_Game"));
            game.setName(rs.getString("Gamename"));
            game.setDate(parseDate((rs.getTimestamp("Gamedate").getTime()))); 
            game.setSolved(rs.getBoolean("Solved"));            
            game.setGameCard(getPlayingCardById(rs.getLong("FS_PlayingCard")));            
            game.setDoneTurns(getTurns(TurnType.Game, game.getId()));
            game.setNoErrorFlag(getNoErrorFlag(game.getId()));    
            game.setDoneFlags(getFlagsByGameId(game.getId(), false));
            return game;
    }

	@Override
	public Game getGameByName(String name) throws SQLException {
		cleanStatement(selectGameByName);
		selectGameByName.setString(1, name);
		ResultSet rs = selectGameByName.executeQuery();		
		while(rs.next()){
			return GameMapping(rs);
		}
		return null;
	}

	@Override
	public PlayingCard getPlayingCardByName(String name) throws SQLException {
		cleanStatement(selectPlayingCardByName);		
		selectPlayingCardByName.setString(1, name);		
		ResultSet rs = selectPlayingCardByName.executeQuery();
		while(rs.next()){
			return PlayingCardMapping(rs);
		}		
		return null;
	}
}
